﻿/*
 * Excel操作帮助类
 * NPOI控件
 * 2010年11月3日
 * William
 */

using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using NPOI.HSSF.UserModel;
using System.Collections.Generic;

namespace Utility
{
    public class ExcelForReport
    {
        public static ExcelForReport Instance = new ExcelForReport();
        StringBuilder sb = new StringBuilder();

        private ExcelForReport() { }

        HSSFWorkbook hssfworkbook;

        public string ConvertToExcel(DataTable dt)
        {
            return null;
        }

        /// <summary>
        /// 初始化工作簿
        /// </summary>
        /// <param name="path"></param>
        private void InitializeWorkbook(string path)
        {
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }

        private object GetCellValue(HSSFCell cell, string format, Type columnType)
        {
            //switch (cell.CellType())
            //{
            //    case HSSFCell.CELL_TYPE_STRING:
            //        strCell = cell.StringCellValue();
            //        break;
            //    case HSSFCell.CELL_TYPE_NUMERIC:
            //        strCell = cell.NumericCellValue());
            //        break;
            //    case HSSFCell.CELL_TYPE_BOOLEAN:
            //        strCell = cell.BooleanCellValue());
            //        break;
            //    case HSSFCell.CELL_TYPE_BLANK:
            //        strCell = "";
            //        break;
            //    default:
            //        strCell = "";
            //        break;
            //}
            string strValue = "";
            if (cell.CellType == HSSFCell.CELL_TYPE_BLANK)
                return DBNull.Value;

            if (format == "int" || format == "int32" || format == "decimal" || format == "double"
                || format == "int64" || format == "int16")
            {
                strValue = cell.NumericCellValue.ToString();
            }
            else if (format == "bool")
            {
                strValue = cell.BooleanCellValue ? "True" : "False";
            }
            else if (format == "datetime")
            {
                strValue = cell.DateCellValue.ToLongDateString();
            }
            else
            {
                strValue = cell.StringCellValue;
            }

            if (columnType == typeof(int))
                return int.Parse(strValue);
            else if (columnType == typeof(decimal))
                return Convert.ToDecimal(strValue);
            else if (columnType == typeof(double))
                return Convert.ToDouble(strValue);
            else if (columnType == typeof(bool))
                return Convert.ToBoolean(strValue);
            else if (columnType == typeof(Int64))
                return Convert.ToInt64(strValue);
            else if (columnType == typeof(Int16))
                return Convert.ToInt16(strValue);
            else if (columnType == typeof(DateTime))
                return Convert.ToDateTime(strValue);
            return strValue;
        }

        /// <summary>
        /// 导出列名
        /// </summary>
        public System.Collections.Generic.Dictionary<string, string> ListColumnsName;

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public void ExportExcel(DataTable dtSource, string filePath)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, filePath);
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public void ExportExcel(DataSet dsSource, string filePath, bool isHead = false)
        {
            //if (ListColumnsName == null || ListColumnsName.Count == 0)
            //    throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            if (dsSource.Tables.Count == 0)
            {
                throw new Exception("报表数据集表为空");
            }

            DataTable dtSheetName = dsSource.Tables[0];
            int tblCount = 0;
            List<string> lstHeader = new List<string>();
            if (dtSheetName.Rows.Count == 1)
            {
                foreach (DataColumn dc in dtSheetName.Columns)
                {
                    lstHeader.Add(dc.ColumnName);
                }
                tblCount = lstHeader.Count;
            }
            else if (dtSheetName.Rows.Count > 1)
            {
                foreach (DataRow dr in dtSheetName.Rows)
                {
                    lstHeader.Add(dr[0].ToString());
                }
                tblCount = lstHeader.Count;
            }
           
            for (int i = 0; i < tblCount; ++i)
            {
                InsertRow(dsSource.Tables[i + 1], lstHeader[i], excelWorkbook, isHead);
            }

            SaveExcelFile(excelWorkbook, filePath);
        }

        /// <summary>
        /// LIST导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public void ExportExcel(IList lstSource, string filePath)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(lstSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, filePath);
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public void ExportExcel(DataTable dtSource, Stream excelStream)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, excelStream);
        }

        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
        {
            FileStream file = null;
            try
            {
                string dir = Path.GetDirectoryName(filePath);
                if (!Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }
                //Directory.CreateDirectory(filePath.Substring(0, filePath.LastIndexOf("/") + 1));
                file = new FileStream(filePath, FileMode.Create);
                excelWorkBook.Write(file);
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }

        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {
            }
        }

        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        protected HSSFWorkbook CreateExcelFile()
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            return hssfworkbook;
        }

        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        protected void CreateHeader(HSSFSheet excelSheet)
        {
            int cellIndex = 0;
            //循环导出列
            foreach (System.Collections.Generic.KeyValuePair<string, string> de in ListColumnsName)
            {
                HSSFRow newRow = excelSheet.CreateRow(0);
                HSSFCell newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                cellIndex++;
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        protected void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
            CreateHeader(newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出10000条数据 创建新的工作簿
                if (rowCount == 10000)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(newsheet);
                }

                HSSFRow newRow = newsheet.CreateRow(rowCount);
                InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        protected void InsertRow(DataTable dtSource, string sheetName, HSSFWorkbook excelWorkbook, bool isHead = false)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;

            //循环数据源导出数据集
            if (dtSource.Rows.Count <= 65535)
                newsheet = excelWorkbook.CreateSheet(sheetName);
            else
                newsheet = excelWorkbook.CreateSheet(sheetName + sheetCount);

            if (isHead)
            {
                ListColumnsName = new System.Collections.Generic.Dictionary<string, string>();
                foreach (DataColumn dc in dtSource.Columns)
                {
                    ListColumnsName[dc.ColumnName] = dc.ColumnName;
                }
                CreateHeader(newsheet);
                rowCount++;
            }
            foreach (DataRow dr in dtSource.Rows)
            {
                //超出10000条数据 创建新的工作簿
                if (rowCount == 65535)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet(sheetName + sheetCount);
                    if (isHead)
                        CreateHeader(newsheet);
                }

                HSSFRow newRow = newsheet.CreateRow(rowCount++);
                InsertCellByColumns(dtSource, dr, newRow, newsheet, excelWorkbook);
            }
        }

        /// <summary>
        /// LIST插入数据行
        /// </summary>
        protected void InsertRow(IList lstSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
            CreateHeader(newsheet);
            foreach (Object obj in lstSource)
            {
                rowCount++;
                if (rowCount == 10000)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(newsheet);
                }

                HSSFRow newRow = newsheet.CreateRow(rowCount);
                InsertCell(lstSource, obj, newRow, newsheet, excelWorkbook);
            }
        }

        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected void InsertCellByColumns(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < dtSource.Columns.Count; cellIndex++)
            {
                string columnsName = dtSource.Columns[cellIndex].ColumnName.ToString();
                HSSFCell newCell = null;

                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                if (drValue.IndexOf('=') == 0)
                {

                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellFormula(drValue.Substring(1));
                    continue;
                }
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }

        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                //string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                ArrayList keys = new ArrayList(ListColumnsName.Keys);
                string columnsName = keys[cellIndex].ToString();
                HSSFCell newCell = null;

                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }

        /// <summary>
        /// list导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected void InsertCell(IList lstSource, Object obj, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                //string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                ArrayList keys = new ArrayList(ListColumnsName.Keys);
                string columnsName = keys[cellIndex].ToString();
                HSSFCell newCell = null;

                //反射获取实体类字段信息
                Type type = obj.GetType();
                PropertyInfo info = type.GetProperty(columnsName);
                System.Type rowType = info.PropertyType;

                object tempObject = info.GetValue(obj, null);
                string drValue = "";
                if (tempObject != null)
                {
                    drValue = tempObject.ToString();
                }

                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }
    }

    //排序实现接口 不进行排序 根据添加顺序导出
    public class NoSortForReport : System.Collections.IComparer
    {
        public int Compare(object x, object y)
        {
            return -1;
        }
    }
}